package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.BookDao;
import bookshop.dao.BrowsHistoryDao;
import bookshop.dao.CategoryDao;
import bookshop.dao.DaoFactory;
import bookshop.domain.Book;
import bookshop.domain.Browsing;
import bookshop.domain.Category;
import bookshop.exception.DataAccessException;
import bookshop.utils.DBUtils;

public class BrowsHistoryDaoJDBCImpl implements BrowsHistoryDao {

	private static String ADD_BROWS_HISTORY="insert into browsing_log(book_code,log_time,session_id,cust_id) values(?,?,?,?)";
	private static String GETBROWS_BYCONNECTION="select * from browsing_log where book_code=? and session_id=?";
	private static String GET_BROWS_BY_SESSIONID="select * from browsing_log where session_id=? order by log_id desc limit 0,6";
	private String SQL(String bookCode,String sessionId) {
		String sql = " select * from book where book_code in( ";
		
	    sql +=" select book_code from browsing_log where session_id in( ";
		
	    sql +=" select session_id from browsing_log where book_code ='"+bookCode+"' and session_id <> '"+sessionId+"') ";
		
	    sql +=" and book_code <> '"+bookCode+"'";
	    
	    sql +=" group by book_code";
	    
	    sql +=" order by count(*) desc";
	    
	    sql +=")";
	    
		sql +=" limit 0,6";
		
		
		return sql;
		
	}
	
	
	public void saveBrowsHistory(Browsing brows){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(ADD_BROWS_HISTORY);
			ps.setString(1, brows.getBook().getBookCode());
			ps.setTimestamp(2, new java.sql.Timestamp(brows.getLogTime()
					.getTime()));
			ps.setString(3, brows.getSessionId());
			ps.setString(4, brows.getCustId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书类别信息保存失败，可能图书类别出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
	}

	public Browsing getBrowsByConnection(String bookCode,String sessionId){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Browsing brows = null;

		try {
			ps = ct.prepareStatement(GETBROWS_BYCONNECTION);
			ps.setString(1, bookCode);
			ps.setString(2, sessionId);
			rs = ps.executeQuery();
			BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
			while(rs.next()){
				brows = new Browsing();
				brows.setLogId(rs.getInt(1));
				brows.setBook(bookDao.getBookByCode(rs.getString(2)));
				brows.setLogTime(new java.util.Date(rs.getTimestamp(3).getTime()));
				brows.setSessionId(rs.getString(4));
				brows.setCustId(rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书类别信息保存失败，可能图书类别出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		return brows;
	}

	public List<Browsing> getBrowssBySessionId(String sessionId){
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Browsing brows = null;
        List<Browsing> browss = new ArrayList<Browsing>();
		try {
			ps = ct.prepareStatement(GET_BROWS_BY_SESSIONID);
			ps.setString(1, sessionId);
			rs = ps.executeQuery();
			BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
			while(rs.next()){
				brows = new Browsing();
				brows.setLogId(rs.getInt(1));
				brows.setBook(bookDao.getBookByCode(rs.getString(2)));
				brows.setLogTime(new java.util.Date(rs.getTimestamp(3).getTime()));
				brows.setSessionId(rs.getString(4));
				brows.setCustId(rs.getString(5));
				browss.add(brows);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书类别信息保存失败，可能图书类别出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		return browss;
	}
	
    public List<Book> getBookByBrows(String bookCode,String sessionId){
    	String sql = this.SQL(bookCode,sessionId);
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Book> books = new ArrayList<Book>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				Book book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);

				books.add(book);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return books;
    	
    }	
}
